package com.imooc.spark

import com.imooc.spark.DataFrameRDDApp.SetLogger
import org.apache.spark.sql.SparkSession

/**
  * Created by zghgchao 2017/12/24 13:21
  * 使用外部数据源查询Hive和MySQL的表数据
  */
object HiveMySQLApp {
  def main(args: Array[String]): Unit = {
    SetLogger()
    val spark = SparkSession.builder()
      .master("local[2]")
      .appName("HiveMySQLApp").getOrCreate()

    // 加载Hive表数据
    val hiveDF = spark.table("emp")
    /**
      *
      *   scala> hiveDF.show
      *     +-----+------+---------+----+----------+-------+------+------+
      *     |empno| ename|      job| mgr|  hiredate|    sal|  comm|deptno|
      *     +-----+------+---------+----+----------+-------+------+------+
      *     | 7369| SMITH|    CLERK|7902|1980-12-17|  800.0|  null|    20|
      *     | 7499| ALLEN| SALESMAN|7698| 1981-2-20| 1600.0| 300.0|    30|
      *     | 7521|  WARD| SALESMAN|7698| 1981-2-22| 1250.0| 500.0|    30|
      *     | 7566| JONES|  MANAGER|7839|  1981-4-2| 2975.0|  null|    20|
      *     | 7654|MARTIN| SALESMAN|7698| 1981-9-28| 1250.0|1400.0|    30|
      *     | 7698| BLAKE|  MANAGER|7839|  1981-5-1| 2850.0|  null|    30|
      *     | 7782| CLARK|  MANAGER|7839|  1981-6-9| 2450.0|  null|    10|
      *     | 7788| SCOTT|  ANALYST|7566| 1987-4-19| 3000.0|  null|    20|
      *     | 7839|  KING|PRESIDENT|null|1981-11-17| 5000.0|  null|    10|
      *     | 7844|TURNER| SALESMAN|7698|  1981-9-8| 1500.0|   0.0|    30|
      *     | 7876| ADAMS|    CLERK|7788| 1987-5-23| 1100.0|  null|    20|
      *     | 7900| JAMES|    CLERK|7698| 1981-12-3|  950.0|  null|    30|
      *     | 7902|  FORD|  ANALYST|7566| 1981-12-3| 3000.0|  null|    20|
      *     | 7934|MILLER|    CLERK|7782| 1982-1-23| 1300.0|  null|    10|
      *     | 8888|  HIVE|  PROGRAM|7839| 1988-1-23|10300.0|  null|  null|
      *     +-----+------+---------+----+----------+-------+------+------+
      */

    // 加载MySQL表数据
    val mySqlDF = spark.read.format("jdbc").option("url", "jdbc:mysql://localhost:3306/").option("dbtable", "spark.DEPT").option("user", "root").option("password", "root").option("driver", "com.mysql.jdbc.Driver").load()
    /**
      * scala> mySqlDF.show
      *   +------+----------+--------+
      *   |DEPTNO|     DNAME|     LOC|
      *   +------+----------+--------+
      *   |    10|ACCOUNTING|NEW YORK|
      *   |    20|  RESEARCH|  DALLAS|
      *   |    30|     SALES| CHICAGO|
      *   |    40|OPERATIONS|  BOSTON|
      *   +------+----------+--------+
      */

    // JOIN
    val resultDF = hiveDF.join(mySqlDF,hiveDF.col("deptno")===mySqlDF.col("DEPTNO"))
    resultDF.show()
    /**
      * scala>resultDF.show()
      *   +-----+------+---------+----+----------+------+------+------+------+----------+--------+
      *   |empno| ename|      job| mgr|  hiredate|   sal|  comm|deptno|DEPTNO|     DNAME|     LOC|
      *   +-----+------+---------+----+----------+------+------+------+------+----------+--------+
      *   | 7934|MILLER|    CLERK|7782| 1982-1-23|1300.0|  null|    10|    10|ACCOUNTING|NEW YORK|
      *   | 7839|  KING|PRESIDENT|null|1981-11-17|5000.0|  null|    10|    10|ACCOUNTING|NEW YORK|
      *   | 7782| CLARK|  MANAGER|7839|  1981-6-9|2450.0|  null|    10|    10|ACCOUNTING|NEW YORK|
      *   | 7902|  FORD|  ANALYST|7566| 1981-12-3|3000.0|  null|    20|    20|  RESEARCH|  DALLAS|
      *   | 7876| ADAMS|    CLERK|7788| 1987-5-23|1100.0|  null|    20|    20|  RESEARCH|  DALLAS|
      *   | 7788| SCOTT|  ANALYST|7566| 1987-4-19|3000.0|  null|    20|    20|  RESEARCH|  DALLAS|
      *   | 7566| JONES|  MANAGER|7839|  1981-4-2|2975.0|  null|    20|    20|  RESEARCH|  DALLAS|
      *   | 7369| SMITH|    CLERK|7902|1980-12-17| 800.0|  null|    20|    20|  RESEARCH|  DALLAS|
      *   | 7900| JAMES|    CLERK|7698| 1981-12-3| 950.0|  null|    30|    30|     SALES| CHICAGO|
      *   | 7844|TURNER| SALESMAN|7698|  1981-9-8|1500.0|   0.0|    30|    30|     SALES| CHICAGO|
      *   | 7698| BLAKE|  MANAGER|7839|  1981-5-1|2850.0|  null|    30|    30|     SALES| CHICAGO|
      *   | 7654|MARTIN| SALESMAN|7698| 1981-9-28|1250.0|1400.0|    30|    30|     SALES| CHICAGO|
      *   | 7521|  WARD| SALESMAN|7698| 1981-2-22|1250.0| 500.0|    30|    30|     SALES| CHICAGO|
      *   | 7499| ALLEN| SALESMAN|7698| 1981-2-20|1600.0| 300.0|    30|    30|     SALES| CHICAGO|
      *   +-----+------+---------+----+----------+------+------+------+------+----------+--------+
      */

    resultDF.select(hiveDF.col("empno"),hiveDF.col("ename"),mySqlDF.col("dname")).show()
    /**
      * scala> resultDF.select(hiveDF.col("empno"),hiveDF.col("ename"),mySqlDF.col("dname")).show()
      *        +-----+------+----------+
      *        |empno| ename|     dname|
      *        +-----+------+----------+
      *        | 7934|MILLER|ACCOUNTING|
      *        | 7839|  KING|ACCOUNTING|
      *        | 7782| CLARK|ACCOUNTING|
      *        | 7902|  FORD|  RESEARCH|
      *        | 7876| ADAMS|  RESEARCH|
      *        | 7788| SCOTT|  RESEARCH|
      *        | 7566| JONES|  RESEARCH|
      *        | 7369| SMITH|  RESEARCH|
      *        | 7900| JAMES|     SALES|
      *        | 7844|TURNER|     SALES|
      *        | 7698| BLAKE|     SALES|
      *        | 7654|MARTIN|     SALES|
      *        | 7521|  WARD|     SALES|
      *        | 7499| ALLEN|     SALES|
      *        +-----+------+----------+
      */

    spark.stop()
  }
}
